package com.example.ccsystem.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.example.ccsystem.entity.Student;

/**
 * Service
 *
 */

@Service
public class StudentServiceImpt implements StudentService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public String getAllStudents(){
        return jdbcTemplate.queryForObject("select count(1) from Student",String.class);
    }

    @Override
    public List<Map<String,Object>> findAllStudent(){
        String sql="select * from Student";
        List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public Student getBySId(int sid){
        String sql="select * from Student where sid=?";
        List<Student> stu=jdbcTemplate.query(sql,new Object[]{sid},new StudentRowMapper());

        Student student=null;
        if(!stu.isEmpty()){
            student=stu.get(0);
        }

        return student;
    }

    /**
     * add student
     * @param student
     * @return
     */
    @Override
    public int addStu(Student student){
        String sql="insert into Student(sid,sname,grade,profession,college) values(null,?,?,?,?)";
        KeyHolder keyHolder=new GeneratedKeyHolder();
        int resRow=jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

                PreparedStatement ps=con.prepareStatement(sql,new String[]{"sid"});
                ps.setString(1,student.getSname());
                ps.setInt(2,student.getGrade());
                ps.setString(3,student.getProfession());
                ps.setString(4,student.getCollege());
                return ps;

            }
        },keyHolder);
        System.out.println("操作记录数："+resRow+"主键："+keyHolder.getKey());
        return Integer.parseInt(keyHolder.getKey().toString());
    }

    @Override
    public int deleteStu(int sid){
        String sql="delete from Student where sid=?";
        return jdbcTemplate.update(sql,sid);
    }

    @Override
    public int updateStu(Student student){
        String sql="update Student set sname=?,grade=?,profession=?,college=? where sid=?";
        int res=jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {

                ps.setString(1,student.getSname());
                ps.setInt(2,student.getGrade());
                ps.setString(3,student.getProfession());
                ps.setString(4,student.getCollege());
            }
        });
        return res;
    }

    @Override
    public int isHasStu(int sid){
        String sql="select * from Student where sid=?";
        List<Student> student=jdbcTemplate.query(sql,new Object[]{sid},
                new StudentRowMapper());

        if(student!=null&&student.size()>0){
            return 1;
        }else {
            return 0;
        }
    }
}

class StudentRowMapper implements RowMapper<Student>{

    @Override
    public Student mapRow(ResultSet resultSet,int i) throws SQLException{

        Student stu=new Student();
        stu.setSid(resultSet.getInt("sid"));
        stu.setSname(resultSet.getString("sname"));
        stu.setGrade(resultSet.getInt("grade"));
        stu.setCollege(resultSet.getString("college"));
        stu.setProfession(resultSet.getString("profession"));

        return stu;
    }
}

